﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Diagnostics;
using Utils;

namespace StockModel
{
    [DebuggerDisplay("Connection = {_dbClient.ConnectionString}")]
    public class StockGroupDataSource : DataSourceBase
    {
        #region Attributes

        private readonly ExchangeMarketDataSource _marketDataSource;

        #endregion

        #region Constants

        private const string StockGroupMarketQuery = "SELECT [ExchangeMarkets].Name FROM [StockGroups] INNER JOIN [ExchangeMarkets] ON [ExchangeMarkets].Id = [StockGroups].MarketId WHERE [StockGroups].Id = {0}";
		private const string StockGroupQuery = "SELECT Id FROM [StockGroups] WITH(NOLOCK) WHERE Name LIKE @Name";
        private const string GetLastId = "SELECT IDENT_CURRENT ('{0}')";
		private const string StocksQuery = "SELECT Id, Symbol, Name FROM Stocks WITH(NOLOCK)";
        private const string StockInsert = "INSERT INTO Stocks VALUES ('{0}','{1}')";

		private const string StocksFromGroupAndDateQuery = "SELECT Stocks.Symbol, Stocks.Name, [StockGroups-Stocks].DateAdded, [StockGroups-Stocks].DateRemoved FROM [StockGroups-Stocks] INNER JOIN Stocks ON [StockGroups-Stocks].IdStock = Stocks.Id WHERE [StockGroups-Stocks].IdStockGroup = {0} AND  ([StockGroups-Stocks].DateRemoved IS NULL OR DATEDIFF(day,CONVERT(smalldatetime, '{1}', 103),[StockGroups-Stocks].DateRemoved) > 0) AND DATEADD(day, DATEDIFF(day, 0, [StockGroups-Stocks].DateAdded), 0) <= CONVERT(smalldatetime, '{1}', 103) ORDER BY Stocks.Name";
		private const string StockGroupStocksQuery = "SELECT  Stocks.Symbol, Stocks.Name, [StockGroups-Stocks].DateAdded, [StockGroups-Stocks].DateRemoved FROM [StockGroups-Stocks] INNER JOIN Stocks ON [StockGroups-Stocks].IdStock = Stocks.Id WHERE IdStockGroup = @Id ORDER BY Stocks.Name";
        private const string StockGroupStocksInsert = "INSERT INTO [StockGroups-Stocks] (IdStockGroup,IdStock,DateAdded) VALUES ({0}, {1}, CONVERT(smalldatetime, '{2}', 103))";
        private const string StockGroupStocksUpdate = "UPDATE [StockGroups-Stocks] SET DateRemoved = CONVERT(smalldatetime, '{0}', 103) WHERE IdStockGroup = {1} AND IdStock = {2} AND DateAdded = CONVERT(smalldatetime, '{3}', 103)";

        #endregion

        #region Public Methods

		public StockGroupDataSource(ExchangeMarketDataSource exchangeMarketDataSource, IDataBaseClient dbClient) : base(dbClient)
        {
			_marketDataSource = exchangeMarketDataSource;
        }

        public void Save(StockGroup stockGroup)
        {
            int stockGroupId = this.GetStockGroupId(stockGroup);

            Dictionary<string, int> dbReadStocks = this.ReadStocksFromDatabase();

            this.InsertMissingStocks(stockGroup, dbReadStocks);

            List<StockGroupItem> dbReadStockGroupLinks = this.DbReadStockGroupLinks(stockGroupId);

        	ObservableCollection2<StockGroupItem> items = stockGroup.Items.GetReadLock();
        	try
        	{
				foreach (StockGroupItem stock in items)
				{
					StockGroupItem item = (from c in dbReadStockGroupLinks
										   where c.Item.Id.Code == stock.Item.Id.Code
										   select c).FirstOrDefault();

					if (item == null)
					{
						// Add missing links
						base.DataBaseClient.ExecuteCommand(string.Format(StockGroupStocksInsert, stockGroupId, dbReadStocks[stock.Item.Id.Code], stock.DateAdded));
					}
					else
					{
						// Update added date of existent items
						stock.DateAdded = item.DateAdded;
					}
				}

				IEnumerable<StockGroupItem> removedItems = dbReadStockGroupLinks.Except(items, new StockGroupLinkComparer()).Where(s => s.DateRemoved == null);

				foreach (StockGroupItem stock in removedItems)
				{
					stock.DateRemoved = DateTime.Now;
					base.DataBaseClient.ExecuteCommand(string.Format(StockGroupStocksUpdate, stock.DateRemoved, stockGroupId, dbReadStocks[stock.Item.Id.Code], stock.DateAdded));
				}
        	}
        	finally
        	{
				stockGroup.Items.FreeReadLock();
        	}

			foreach (StockGroupItem dbReadStockGroupLink in dbReadStockGroupLinks)
        	{
        		dbReadStockGroupLink.Item.Dispose();
        	}
        }

        public void Load(StockGroup group, DateTime day)
		{
            int stockGroupId = this.GetStockGroupId(group);

			DataTable stocksRead = base.DataBaseClient.ExecuteQuery(string.Format(StocksFromGroupAndDateQuery, stockGroupId, day.Date));

			List<StockGroupItem> tempItems = new List<StockGroupItem>();

			foreach (DataRow row in stocksRead.Rows)
			{
				Stock stock = new Stock(row["Symbol"].ToString(), row["Name"].ToString());
                object dateRemovedTemp = row["DateRemoved"];
                DateTime? dateRemoved = dateRemovedTemp is DBNull ? null : (DateTime?)dateRemovedTemp;
                StockGroupItem stockGroupItem = new StockGroupItem(stock, (DateTime)row["DateAdded"], dateRemoved);
				
				stock.QuotationCalendar.CreateDay(day);
                tempItems.Add(stockGroupItem);
			}

			// Hack to add all the items at once and raise less events and have the lock less time
        	ObservableCollection2<StockGroupItem> groupItems = group.Items.GetWriteLock();
        	try
        	{
				groupItems.AddRange(tempItems);
        	}
        	finally 
        	{
				group.Items.FreeWriteLock();
        	}
		}

		public void LoadMarket(StockGroup group)
		{
			int stockGroupId = this.GetStockGroupId(group);
			group.Market = this.GetMarket(stockGroupId);
		}

    	#endregion

        #region Private Methods

        private int GetStockGroupId(StockGroup stockGroup)
        {
			object stockGroupId = base.DataBaseClient.ExecuteScalar(StockGroupQuery, new string[] { "@Name" }, new object[] { stockGroup.Name.ToString() });

            if (stockGroupId == null)
            {
                throw new System.Data.DataException(string.Format("The stock group {0} cannot be found at the database", stockGroup.Name));
            }

            return Convert.ToInt32(stockGroupId);
        }

        private ExchangeMarket GetMarket(int stockGroupId)
        {
			string marketName = base.DataBaseClient.ExecuteScalar(string.Format(StockGroupMarketQuery, stockGroupId)).ToString();
            return _marketDataSource.Load((ExchangeMarketName)Enum.Parse(typeof(ExchangeMarketName),marketName));
        }

        private Dictionary<string, int> ReadStocksFromDatabase()
        {
			DataTable dataRead = base.DataBaseClient.ExecuteQuery(StocksQuery);

            return dataRead.Rows.Cast<DataRow>().ToDictionary(row => row["Symbol"].ToString(), row => Convert.ToInt32(row["Id"]));
        }

        private void InsertMissingStocks(StockGroup stockGroup, IDictionary<string, int> dbReadStocks)
        {
			IEnumerable<StockGroupItem> missingItems = stockGroup.Items.GetReadLock();
        	try
        	{
				missingItems = missingItems.Where(stock => !dbReadStocks.ContainsKey(stock.Item.Id.Code));
        	}
        	finally
        	{
				stockGroup.Items.FreeReadLock();	
        	}

            foreach (StockGroupItem missingItem in missingItems)
            {
				base.DataBaseClient.ExecuteCommand(string.Format(StockInsert, missingItem.Item.Id.Code, missingItem.Item.Id.Name));
				int tempId = Convert.ToInt32(base.DataBaseClient.ExecuteScalar(string.Format(GetLastId, "Stocks")));
				dbReadStocks.Add(missingItem.Item.Id.Code, tempId);
            }
        }

        private List<StockGroupItem> DbReadStockGroupLinks(int stockGroupId)
        {
            List<StockGroupItem> result = new List<StockGroupItem>();

			DataTable dataRead = base.DataBaseClient.ExecuteQuery(StockGroupStocksQuery, new string[] { "@Id" }, new object[] { stockGroupId });

            foreach (DataRow row in dataRead.Rows)
            {
                object dateRemovedTemp = row["DateRemoved"];
                DateTime? dateRemoved = dateRemovedTemp is DBNull ? null : (DateTime?)dateRemovedTemp;
                result.Add(new StockGroupItem(new Stock(row["Symbol"].ToString(), row["Name"].ToString()), (DateTime)row["DateAdded"], dateRemoved));
            }

            return result;
        }

        #endregion

        #region StockGroupLinkComparer

        private class StockGroupLinkComparer : IEqualityComparer<StockGroupItem>
        {
            public bool Equals(StockGroupItem x, StockGroupItem y)
            {
				return x.Item.Id.Code == y.Item.Id.Code;
            }

            public int GetHashCode(StockGroupItem obj)
            {
				return obj.Item.Id.Code.GetHashCode();
            }
        }

        #endregion
    }

}
